
--
-- Create LastFlightExtractDate as beginning of time if 
-- it does not already exist!
--
create table LastFlightExtractDate
  as select trunc(to_date('0001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'), 'DDD') as LastExtractDate
    from dual
;

--
-- Extract flights since last extract!
--
create table FlightsSinceExtract
  as 
    select   'SG70' as Club
            , sg70.LaunchTime
            , sg70.LandingTime
            , sg70.PlaneRegistration
            , sg70.Pilot1Init
            , sg70.Pilot2Init
            , sg70.LaunchAeroTow
            , sg70.LaunchWinch
            , sg70.LaunchSelfLaunch
            , sg70.CableBreak
            , sg70.CrossCountryKm
    from taFlightsSg70 sg70
    where sg70.LaunchTime > (select LastExtractDate from LastFlightExtractDate)
union
    select   'Vejle' as Club
            , vejle.LaunchTime
            , vejle.LandingTime
            , vejle.PlaneRegistration
            , vejle.Pilot1Init
            , vejle.Pilot2Init
            , vejle.LaunchAeroTow
            , vejle.LaunchWinch
            , vejle.LaunchSelfLaunch
            , vejle.CableBreak
            , vejle.CrossCountryKm
    from taFlightsVejle vejle
    where vejle.LaunchTime > (select LastExtractDate from LastFlightExtractDate)
;

create table FlightErrors
  as
    select '                       ' as Reason
          , Club
          , LaunchTime
          , LandingTime
          , PlaneRegistration
          , Pilot1Init
          , Pilot2Init
          , LaunchAeroTow
          , LaunchWinch
          , LaunchSelfLaunch
          , CableBreak
          , CrossCountryKm
    from FlightsSinceExtract
    where 1=0
;

--
-- Counter number of extracted flights
--
drop table ExtractFlightCount;
create table ExtractFlightCount
  as
    select count(*) as Counter
      from FlightsSinceExtract
;

--
-- Count number of facts before operation
--
drop table Fact_FlightCountBefore;
create table Fact_FlightCountBefore
  as 
    select count(*) as Counter
      from Fact_Flight
;

--
-- Counter number of errors before operation
--
drop table FlightErrorsCountBefore;
create table FlightErrorsCountBefore
  as
    select count(*) as Counter
      from FlightErrors
;

--
-- Create error table with flights that are either too short
-- or too long.
--
insert into FlightErrors
    select 'Invalid Duration' as Reason
          , Club
          , LaunchTime
          , LandingTime
          , PlaneRegistration
          , Pilot1Init
          , Pilot2Init
          , LaunchAeroTow
          , LaunchWinch
          , LaunchSelfLaunch
          , CableBreak
          , CrossCountryKm
    from FlightsSinceExtract
    where LandingTime > LaunchTime + to_dsinterval('001 00:00:00')
      or LandingTime < LaunchTime + to_dsinterval('000 00:05:00')
;

--
-- Remove the rows with flights that are either too short
-- or too long
--
delete from FlightsSinceExtract
  where LandingTime > LaunchTime + to_dsinterval('001 00:00:00')
    or LandingTime < LaunchTime + to_dsinterval('000 00:05:00')
;

-- 
-- Insert rows with an invalid distance to the error table
--
insert into FlightErrors
  select    'Invalid Distance' as Reason
          , Club
          , LaunchTime
          , LandingTime
          , PlaneRegistration
          , Pilot1Init
          , Pilot2Init
          , LaunchAeroTow
          , LaunchWinch
          , LaunchSelfLaunch
          , CableBreak
          , CrossCountryKm
    from  FlightsSinceExtract
    where CrossCountryKm > 3000 or CrossCountryKm < 0
;

--
-- Delete rows with an invalid distance
--
delete from FlightsSinceExtract
  where CrossCountryKm > 3000 or CrossCountryKm < 0
;

--
-- Inserts rows with an invalid launch type to the error table
--
insert into FlightErrors
  select    'Invalid LaunchType' as Reason
          , Club
          , LaunchTime
          , LandingTime
          , PlaneRegistration
          , Pilot1Init
          , Pilot2Init
          , LaunchAeroTow
          , LaunchWinch
          , LaunchSelfLaunch
          , CableBreak
          , CrossCountryKm
    from FlightsSinceExtract
    where 'invalid' =
                    (
                      case
                        when lower(LaunchAeroTow) = 'y' and lower(LaunchWinch) = 'n' and lower(LaunchSelfLaunch) = 'n' then 'valid'
                        when lower(LaunchAeroTow) = 'n' and lower(LaunchWinch) = 'y' and lower(LaunchSelfLaunch) = 'n' then 'valid'
                        when lower(LaunchAeroTow) = 'n' and lower(LaunchWinch) = 'n' and lower(LaunchSelfLaunch) = 'y' and lower(CableBreak) = 'n' then 'valid'
                        else 'invalid'
                      end
                    )
;

--
-- Delete rows with an invalid launch type 
--
delete from FlightsSinceExtract
  where 'invalid' =
                  (
                    case
                      when lower(LaunchAeroTow) = 'y' and lower(LaunchWinch) = 'n' and lower(LaunchSelfLaunch) = 'n' then 'valid'
                      when lower(LaunchAeroTow) = 'n' and lower(LaunchWinch) = 'y' and lower(LaunchSelfLaunch) = 'n' then 'valid'
                      when lower(LaunchAeroTow) = 'n' and lower(LaunchWinch) = 'n' and lower(LaunchSelfLaunch) = 'y' and lower(CableBreak) = 'n' then 'valid'
                      else 'invalid'
                    end
                  )
;

--
-- All errors has now been removed. (hopefully!)
-- Use the following SQL block to do insertion
--
declare
  --
  -- Cursor for all non-error flights
  --
  cursor flight_additions is
    select *
    from FlightsSinceExtract;
begin
  --
  -- Loop through all non-error flight records
  --
  for flight_row in flight_additions loop
    declare
      pilot1Id Dim_Member.MemberId%type := -1;
      pilot2Id Dim_Member.MemberId%type := -2;
    
      --
      -- The generated id that will be used for the bridge table
      --
      memberGroupId Bridge_MemberGroup.MemberGroupId%type := membergroup_seq.nextval;
    begin
      --
      -- Attempt to get the ID for pilot 1
      --
      begin
        select MemberId 
          into pilot1Id 
          from Dim_Member 
          where flight_row.Pilot1Init = Initials and ValidTo = to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS');
      exception
        when others then null;
      end;
      
      --
      -- If pilot 2 exists, attempt to get ID for pilot 2
      --
      if flight_row.Pilot2Init is not null then
        begin
          select MemberId 
            into pilot2Id 
            from Dim_Member 
            where flight_row.Pilot2Init = Initials and ValidTo = to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS');
        exception
          when others then null;
        end;
      end if;
      
      --
      -- If only pilot 1 is used
      --
      if flight_row.Pilot2Init is null then
        --
        -- Just insert the single pilot id directly into the Bridge_MemberGroup table
        --
        insert into Bridge_MemberGroup
          (
              MemberGroupId
            , MemberId
			, Weight
          )
        values
          (
              memberGroupId
            , pilot1Id
			, 1.0
          );
      else
        --
        -- Else, if both pilots are used!
        --
        begin
          --
          -- Insert both pilot ids into the Bridge_MemberGroup table
          --
          insert into Bridge_MemberGroup
            (
                MemberGroupId
              , MemberId
			  , Weight
            )
          values
            (
                memberGroupId
              , pilot1Id
			  , 0.5
            );
                                        
          insert into Bridge_MemberGroup
            (
                MemberGroupId
              , MemberId
			  , Weight
            )
          values
            (
                memberGroupId
              , pilot2Id
			  , 0.5
            );
        end;
      end if;
      
      --
      -- We have a Bridge_MemberGroup for our flight!
      --
      declare
        row_to_insert Fact_Flight%rowtype;
      begin
        --
        -- Set unused dimension columns
        --
        row_to_insert.ClubId_FK := -1;
        row_to_insert.GliderId_FK := -1;
        
        --
        -- Set used dimension columns
        --
        select TimeId 
          into row_to_insert.LaunchTimeId_FK
          from Dim_Time
          where FullDate = flight_row.LaunchTime;
        
        select FlightStartTypeId
          into row_to_insert.FlightStartTypeId_FK
          from Dim_FlightStartType
          where LaunchType =
            (
              case
                when lower(flight_row.LaunchAeroTow) = 'n' and lower(flight_row.LaunchWinch) = 'n' and lower(flight_row.LaunchSelfLaunch) = 'y' and lower(flight_row.CableBreak) = 'n' then 'Self Launch'
                when lower(flight_row.LaunchAeroTow) = 'y' and lower(flight_row.LaunchWinch) = 'n' and lower(flight_row.LaunchSelfLaunch) = 'n' and lower(flight_row.CableBreak) = 'n' then 'Aero Tow'
                when lower(flight_row.LaunchAeroTow) = 'y' and lower(flight_row.LaunchWinch) = 'n' and lower(flight_row.LaunchSelfLaunch) = 'n' and lower(flight_row.CableBreak) = 'y' then 'Aero Tow (Cable Break)'
                when lower(flight_row.LaunchAeroTow) = 'n' and lower(flight_row.LaunchWinch) = 'y' and lower(flight_row.LaunchSelfLaunch) = 'n' and lower(flight_row.CableBreak) = 'n' then 'Winch'
                when lower(flight_row.LaunchAeroTow) = 'n' and lower(flight_row.LaunchWinch) = 'y' and lower(flight_row.LaunchSelfLaunch) = 'n' and lower(flight_row.CableBreak) = 'y' then 'Winch (Cable Break)'
                else '(unknown)'
              end
            );
          
        row_to_insert.MemberGroupId_FK := memberGroupId;
        
        --
        -- Calculate and set facts
        --
        row_to_insert.distance := flight_row.CrossCountryKm;
        row_to_insert.duration := (flight_row.LandingTime - flight_row.LaunchTime) day to second;
        
        --
        -- Insert row to fact!
        --
        insert into Fact_Flight
          (
              LaunchTimeId_FK
            , MemberGroupId_FK
            , ClubId_FK
            , GliderId_FK
            , FlightStartTypeId_FK
            , Duration
            , Distance
          )
        values
          (
              row_to_insert.LaunchTimeId_FK
            , row_to_insert.MemberGroupId_FK
            , row_to_insert.ClubId_FK
            , row_to_insert.GliderId_FK
            , row_to_insert.FlightStartTypeId_FK
            , row_to_insert.Duration
            , row_to_insert.Distance
          );
        
      end;
    end;
  end loop;
  
  commit;
end;
/

--
-- Count number of facts after operation
--
drop table Fact_FlightCountAfter;
create table Fact_FlightCountAfter
  as 
    select count(*) as Counter
      from Fact_Flight
;

--
-- Counter number of errors after operation
--
drop table FlightErrorsCountAfter;
create table FlightErrorsCountAfter
  as
    select count(*) as Counter
      from FlightErrors
;

--
-- Compare the counted value to make sure they make sense. 
-- The variables are:
--
-- [FactCountBefore]
select Counter
  from Fact_FlightCountBefore;

-- [FactCountAfter]
select Counter
  from Fact_FlightCountAfter;
  
-- [ErrorCountBefore]
select Counter
  from FlightErrorsCountBefore;
  
-- [ErrorCountAfter]
select Counter
  from FlightErrorsCountAfter;
  
-- [NewFlights]
select Counter
  from ExtractFlightCount;
  
--
-- With the following variables:
-- [Errors] = [ErrorCountAfter] - [ErrorCountBefore]
-- [Added] = [FactCountAfter] - [FactCountBefore] 
-- 
-- The following must be true:
--
-- [NewFlights] - [Added] == [Errors]
--

--
-- Drop the table used to add facts
--
drop table FlightsSinceExtract
;

--
-- Drop last extract date table
--
drop table LastFlightExtractDate
;

--
-- Create last extract date table as today!
--
create table LastFlightExtractDate
  as select trunc(sysdate, 'DDD') as LastExtractDate
    from dual
;

commit
;